Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 25
Using EXPLAIN PLAN and SQL Trace

Probably the best way to determine whether your SQL statements are properly optimized is by using the Oracle SQL Trace facility and the EXPLAIN PLAN command. You can use the SQL Trace facility and the Oracle program TKPROF, which is used to translate trace files, to trace production SQL statements, and gather statistics about those statements.

You use SQL Trace to gather information into a trace file; the Oracle program TKPROF formats the trace information into useful, understandable data.

The EXPLAIN PLAN command is used to display the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. By analyzing the execution plan the Oracle optimizer has chosen, and knowing your data and application, you should be able to determine whether the optimizer has chosen the correct execution plan for your application.

After using EXPLAIN PLAN, you can rewrite your SQL statements to take better advantage of such things as indexes and hash keys. By analyzing the output, you may be able to provide hints that the Oracle optimizer can use to take better advantage of your knowledge of your data. By using hints, you may be able to take better advantage of features such as the Oracle Parallel Query option.

By the end of this chapter, you should be able to execute SQL statements using both SQL Trace and EXPLAIN PLAN and be able to analyze the output from these statements. You should also understand the value of registering applications for later use when tracking performance problems. These Oracle options can greatly improve the stability and performance of your system.

SQL Trace

The SQL Trace facility and the Oracle program TKPROF are designed to give performance information about individual SQL statements. You can use this information to determine the characteristics of those statements.

You can enable SQL Trace for a session or for an entire instance. Of course, because this facility gathers an abundance of information about SQL statement functionality and performance, SQL Trace has an effect on the performance of the system. If you use SQL Trace on a single session, the effect is fairly minimal, but if you use SQL Trace on an entire instance, you will see a substantial effect on the performance of the system. Avoid running SQL Trace on an entire instance for this reason.

SQL Trace Initialization

Before you run SQL Trace, you must make sure that certain Oracle initialization parameters are set:


Parameter Description

TIMED_STATISTICS Setting TIMED_STATISTICS to TRUE enables SQL Trace and some of the dynamic performance tables to collect timed statistics such as CPU and elapsed times. Enabling timed statistics incurs significant overhead because most Oracle operations are now being timed; avoid this parameter except when necessary.
MAX_DUMP_FILE_SIZE Specifies the maximum size of trace file dumps in OS blocks. Set this fairly low to avoid filling up the file system with trace files. If the SQL Trace output files are being truncated, increase this value.
USER_DUMP_DEST This parameter specifies the destination for the trace file. The default destination is the same as for system dumps on your OS.

Controlling SQL Trace

You can enable the SQL Trace facility on a per-session basis or for the entire instance. The following sections explain how to enable and disable SQL Trace for both of these cases.

Enable SQL Trace for a Session

To enable SQL Trace for a session, use this Oracle command:

ALTER SESSION
SET SQL TRACE = TRUE;

Alternatively, you can use the Oracle procedure RDBMS_SESSION.SET_SQL_TRACE.

To enable SQL Trace for a session other than your own, you can use the Oracle procedure RDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION with the arguments SID, Serial#, and TRUE. To determine the values for SID and Serial#, use the following SQL statements:

SQL> SELECT sid, serial#, osuser
  2   FROM v$session
  3   WHERE osuser = 'Ed Whalen';
      SID   SERIAL# OSUSER
----------- ------- ------------
        7         4 Ed Whalen

To turn SQL Trace on for that session, use the Oracle stored procedure as follows:

SQL> EXECUTE RDBMS_system.set_sql_trace_in_session(7,4,TRUE);
PL/SQL procedure successfully completed.

Disable SQL Trace for a Session

To disable SQL Trace for a session, use this Oracle command:

ALTER SESSION
SET SQL TRACE = FALSE;

The SQL Trace facility is also disabled when your session disconnects from Oracle.

To disable SQL Trace for a session other than your own, use the Oracle procedure RDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION with the arguments SID, Serial#, and FALSE as shown here:

SQL> EXECUTE RDBMS_system.set_sql_trace_in_session(7,4,FALSE);
PL/SQL procedure successfully completed.

Enable SQL Trace for an Instance

To enable SQL Trace for your instance, set the Oracle initialization parameter SQL_TRACE to TRUE. Doing so enables SQL Trace for all users of this instance for the duration of the instance.

Disable SQL Trace for an Instance

The SQL Trace facility cannot be disabled for the entire instance without shutting down the Oracle instance and setting the Oracle initialization parameter SQL_TRACE to FALSE. Alternatively, you can remove the parameter because its default value is FALSE.

When SQL Trace is enabled for the entire instance, it is still possible to disable it on a per-session basis. You can disable SQL Trace on a per-session basis with the SQL statement shown in the preceding section.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.